#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库管理模块 - 作业记录和统计数据持久化
"""
import sqlite3
import json
from datetime import datetime, timedelta
import threading
import os

class Database:
    def __init__(self, db_path='data/farm_data.db'):
        self.db_path = db_path
        self.lock = threading.Lock()
        
        # 确保data目录存在
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        
        # 初始化数据库
        self.init_database()
    
    def get_connection(self):
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # 返回字典形式的结果
        return conn
    
    def init_database(self):
        """初始化数据库表结构"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            # 作业记录表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS work_sessions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    robot_id TEXT NOT NULL,
                    field_id TEXT,
                    path_id TEXT,
                    start_time TIMESTAMP NOT NULL,
                    end_time TIMESTAMP,
                    duration_seconds INTEGER,
                    area_covered REAL DEFAULT 0,
                    distance_traveled REAL DEFAULT 0,
                    fuel_consumed REAL DEFAULT 0,
                    status TEXT DEFAULT 'running',
                    start_lat REAL,
                    start_lon REAL,
                    end_lat REAL,
                    end_lon REAL,
                    notes TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 任务表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS tasks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    task_id TEXT UNIQUE NOT NULL,
                    robot_id TEXT NOT NULL,
                    field_id TEXT,
                    path_id TEXT NOT NULL,
                    task_type TEXT DEFAULT 'auto',
                    status TEXT DEFAULT 'pending',
                    priority INTEGER DEFAULT 5,
                    assigned_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    started_time TIMESTAMP,
                    completed_time TIMESTAMP,
                    progress INTEGER DEFAULT 0,
                    work_params TEXT,
                    result TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # GPS轨迹记录表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS gps_tracks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    session_id INTEGER,
                    robot_id TEXT NOT NULL,
                    latitude REAL NOT NULL,
                    longitude REAL NOT NULL,
                    altitude REAL,
                    speed REAL,
                    heading REAL,
                    satellites INTEGER,
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (session_id) REFERENCES work_sessions(id)
                )
            ''')
            
            # 传感器数据记录表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS sensor_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    session_id INTEGER,
                    robot_id TEXT NOT NULL,
                    sensor_type TEXT NOT NULL,
                    data TEXT,
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (session_id) REFERENCES work_sessions(id)
                )
            ''')
            
            # 系统事件日志表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS event_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    robot_id TEXT,
                    event_type TEXT NOT NULL,
                    level TEXT DEFAULT 'info',
                    message TEXT,
                    data TEXT,
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建索引
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_work_sessions_robot ON work_sessions(robot_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_work_sessions_time ON work_sessions(start_time)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_tasks_robot ON tasks(robot_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_gps_tracks_session ON gps_tracks(session_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_event_logs_robot ON event_logs(robot_id)')
            
            conn.commit()
            conn.close()
    
    # ==================== 作业会话管理 ====================
    
    def start_work_session(self, robot_id, field_id=None, path_id=None, lat=None, lon=None):
        """开始新的作业会话"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO work_sessions 
                (robot_id, field_id, path_id, start_time, status, start_lat, start_lon)
                VALUES (?, ?, ?, ?, 'running', ?, ?)
            ''', (robot_id, field_id, path_id, datetime.now(), lat, lon))
            
            session_id = cursor.lastrowid
            conn.commit()
            conn.close()
            
            self.log_event(robot_id, 'work_start', 'info', 
                          f'开始作业 - 地块:{field_id}, 路径:{path_id}',
                          {'session_id': session_id})
            
            return session_id
    
    def end_work_session(self, session_id, area_covered=0, distance_traveled=0, 
                        fuel_consumed=0, lat=None, lon=None, notes=None):
        """结束作业会话"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            # 获取开始时间计算时长
            cursor.execute('SELECT start_time, robot_id FROM work_sessions WHERE id = ?', (session_id,))
            row = cursor.fetchone()
            if row:
                start_time = datetime.fromisoformat(row['start_time'])
                duration = int((datetime.now() - start_time).total_seconds())
                robot_id = row['robot_id']
                
                cursor.execute('''
                    UPDATE work_sessions 
                    SET end_time = ?, duration_seconds = ?, area_covered = ?,
                        distance_traveled = ?, fuel_consumed = ?, status = 'completed',
                        end_lat = ?, end_lon = ?, notes = ?
                    WHERE id = ?
                ''', (datetime.now(), duration, area_covered, distance_traveled, 
                     fuel_consumed, lat, lon, notes, session_id))
                
                conn.commit()
                
                self.log_event(robot_id, 'work_end', 'info',
                              f'完成作业 - 时长:{duration}秒, 面积:{area_covered}亩',
                              {'session_id': session_id, 'duration': duration})
            
            conn.close()
    
    def get_active_session(self, robot_id):
        """获取机器人的活跃作业会话"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT * FROM work_sessions 
                WHERE robot_id = ? AND status = 'running'
                ORDER BY start_time DESC LIMIT 1
            ''', (robot_id,))
            
            row = cursor.fetchone()
            conn.close()
            
            return dict(row) if row else None
    
    def get_work_history(self, robot_id=None, limit=50, status=None):
        """获取作业历史记录"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            query = 'SELECT * FROM work_sessions WHERE 1=1'
            params = []
            
            if robot_id:
                query += ' AND robot_id = ?'
                params.append(robot_id)
            
            if status:
                query += ' AND status = ?'
                params.append(status)
            
            query += ' ORDER BY start_time DESC LIMIT ?'
            params.append(limit)
            
            cursor.execute(query, params)
            rows = cursor.fetchall()
            conn.close()
            
            return [dict(row) for row in rows]
    
    # ==================== 任务管理 ====================
    
    def create_task(self, task_id, robot_id, path_id, field_id=None, 
                   task_type='auto', priority=5, work_params=None):
        """创建新任务"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            params_json = json.dumps(work_params) if work_params else None
            
            try:
                cursor.execute('''
                    INSERT INTO tasks 
                    (task_id, robot_id, field_id, path_id, task_type, priority, work_params)
                    VALUES (?, ?, ?, ?, ?, ?, ?)
                ''', (task_id, robot_id, field_id, path_id, task_type, priority, params_json))
                
                conn.commit()
                self.log_event(robot_id, 'task_created', 'info',
                              f'创建任务 - {task_id}', {'task_id': task_id})
                return True
            except sqlite3.IntegrityError:
                return False
            finally:
                conn.close()
    
    def update_task_status(self, task_id, status, progress=None, result=None):
        """更新任务状态"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            updates = ['status = ?']
            params = [status]
            
            if status == 'running' and progress is None:
                updates.append('started_time = ?')
                params.append(datetime.now())
            
            if status == 'completed':
                updates.append('completed_time = ?')
                params.append(datetime.now())
                if progress is None:
                    progress = 100
            
            if progress is not None:
                updates.append('progress = ?')
                params.append(progress)
            
            if result:
                updates.append('result = ?')
                params.append(json.dumps(result))
            
            params.append(task_id)
            
            query = f'UPDATE tasks SET {", ".join(updates)} WHERE task_id = ?'
            cursor.execute(query, params)
            
            conn.commit()
            conn.close()
    
    def get_tasks(self, robot_id=None, status=None, limit=50):
        """获取任务列表"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            query = 'SELECT * FROM tasks WHERE 1=1'
            params = []
            
            if robot_id:
                query += ' AND robot_id = ?'
                params.append(robot_id)
            
            if status:
                query += ' AND status = ?'
                params.append(status)
            
            query += ' ORDER BY priority DESC, assigned_time DESC LIMIT ?'
            params.append(limit)
            
            cursor.execute(query, params)
            rows = cursor.fetchall()
            conn.close()
            
            tasks = []
            for row in rows:
                task = dict(row)
                if task['work_params']:
                    task['work_params'] = json.loads(task['work_params'])
                if task['result']:
                    task['result'] = json.loads(task['result'])
                tasks.append(task)
            
            return tasks
    
    # ==================== GPS轨迹记录 ====================
    
    def log_gps_track(self, robot_id, lat, lon, session_id=None, 
                     altitude=None, speed=None, heading=None, satellites=None):
        """记录GPS轨迹点"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO gps_tracks 
                (session_id, robot_id, latitude, longitude, altitude, speed, heading, satellites)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ''', (session_id, robot_id, lat, lon, altitude, speed, heading, satellites))
            
            conn.commit()
            conn.close()
    
    def get_gps_track(self, session_id):
        """获取指定会话的GPS轨迹"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT * FROM gps_tracks 
                WHERE session_id = ?
                ORDER BY timestamp ASC
            ''', (session_id,))
            
            rows = cursor.fetchall()
            conn.close()
            
            return [dict(row) for row in rows]
    
    # ==================== 事件日志 ====================
    
    def log_event(self, robot_id, event_type, level, message, data=None):
        """记录系统事件"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            data_json = json.dumps(data, ensure_ascii=False) if data else None
            
            cursor.execute('''
                INSERT INTO event_logs (robot_id, event_type, level, message, data)
                VALUES (?, ?, ?, ?, ?)
            ''', (robot_id, event_type, level, message, data_json))
            
            conn.commit()
            conn.close()
    
    def get_event_logs(self, robot_id=None, level=None, limit=100):
        """获取事件日志"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            query = 'SELECT * FROM event_logs WHERE 1=1'
            params = []
            
            if robot_id:
                query += ' AND robot_id = ?'
                params.append(robot_id)
            
            if level:
                query += ' AND level = ?'
                params.append(level)
            
            query += ' ORDER BY timestamp DESC LIMIT ?'
            params.append(limit)
            
            cursor.execute(query, params)
            rows = cursor.fetchall()
            conn.close()
            
            return [dict(row) for row in rows]
    
    # ==================== 统计查询 ====================
    
    def get_daily_stats(self, date=None):
        """获取每日统计数据"""
        if date is None:
            date = datetime.now().date()
        
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT 
                    COUNT(*) as session_count,
                    SUM(duration_seconds) as total_duration,
                    SUM(area_covered) as total_area,
                    SUM(distance_traveled) as total_distance,
                    SUM(fuel_consumed) as total_fuel,
                    COUNT(DISTINCT robot_id) as robot_count
                FROM work_sessions
                WHERE DATE(start_time) = ? AND status = 'completed'
            ''', (date,))
            
            row = cursor.fetchone()
            conn.close()
            
            return dict(row) if row else {}
    
    def get_weekly_stats(self, weeks=4):
        """获取最近几周的统计数据"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            end_date = datetime.now().date()
            start_date = end_date - timedelta(weeks=weeks)
            
            cursor.execute('''
                SELECT 
                    strftime('%Y-%W', start_time) as week,
                    COUNT(*) as session_count,
                    SUM(duration_seconds) as total_duration,
                    SUM(area_covered) as total_area,
                    SUM(distance_traveled) as total_distance
                FROM work_sessions
                WHERE DATE(start_time) >= ? AND status = 'completed'
                GROUP BY week
                ORDER BY week DESC
            ''', (start_date,))
            
            rows = cursor.fetchall()
            conn.close()
            
            return [dict(row) for row in rows]
    
    def get_robot_stats(self, robot_id, days=30):
        """获取指定机器人的统计数据"""
        with self.lock:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            start_date = datetime.now().date() - timedelta(days=days)
            
            cursor.execute('''
                SELECT 
                    COUNT(*) as session_count,
                    SUM(duration_seconds) as total_duration,
                    SUM(area_covered) as total_area,
                    SUM(distance_traveled) as total_distance,
                    SUM(fuel_consumed) as total_fuel,
                    AVG(duration_seconds) as avg_duration
                FROM work_sessions
                WHERE robot_id = ? AND DATE(start_time) >= ? AND status = 'completed'
            ''', (robot_id, start_date))
            
            row = cursor.fetchone()
            conn.close()
            
            return dict(row) if row else {}

# 创建全局数据库实例
db = Database()

